package com.cityeye.dao;

import java.util.Calendar;
import java.util.Collections;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import com.cityeye.form.QueryForm;

public class QueryDao {

	protected static SessionFactory sf;
	public static final java.text.SimpleDateFormat dateFormater = new java.text.SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss");

	static {
		try {
			sf = new Configuration().configure().buildSessionFactory();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public SessionFactory getSessionFactory() {
		return sf;
	}

	public SessionFactory setSessionFactory(SessionFactory sfac) {
		return sf = sfac;
	}

	public static List queryAll(QueryForm form) {
		List result = Collections.emptyList();
		sf.getCurrentSession().beginTransaction();
		try {
			SQLQuery qSuccess = sf.getCurrentSession().createSQLQuery(
					getQuerySQL(form));
			result = qSuccess.list();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sf.getCurrentSession().getTransaction().commit();
		}
		return result;
	}

	// Query base on JieDao.
	// DeptId: Total # Success completed: Total points.
	public static String getQuerySQL(QueryForm form) {
		// basic select.
		StringBuffer sbSql = new StringBuffer("SELECT m1.JIEDAO, m1.ASSIGNED_QUDEPT_ID, count(*) as successnum,SUM(s1.POINT) as winpoints FROM MMESSAGE m1, SUBISSUETYPE s1 where m1.msgstate=");
		sbSql.append(form.getMsgStatus());
		sbSql.append(" and m1.SUBISSUETYPE_ID=s1.ID ");
		
		// Criteria: IssueType/SubIssueType
		if (form.getIssueType()!=null && form.getIssueType()>=0 ) {
			sbSql.append( " and s1.ISSUETYPE_ID=" +form.getIssueType() +" ");
		}else if(form.getSubIssueType()!=null && form.getSubIssueType()>=0 ){
			sbSql.append( " and m1.SUBISSUETYPE_ID=" +form.getSubIssueType() +" ");			
		}
		
		// Criteria: belong to a Qu
		if (form.getQuDepartmentId()!=null && form.getQuDepartmentId()>=0 ) {
			sbSql.append( " and m1.ASSIGNED_QUDEPT_ID=" +form.getQuDepartmentId() +" ");	
		}		

		// Criteria: Start/End Time.
		String startTime = form.getStartTime();
		String endTime = form.getEndTime();

		if (startTime != null) {
//			String startTimeStr = dateFormater.format(startTime);
//			String endTimeStr = dateFormater.format(Calendar.getInstance()
//					.getTime());
			if (endTime == null) {
				endTime = dateFormater.format(Calendar.getInstance());
			}
			sbSql.append( " and m1.ARRIVETIME BETWEEN '" + startTime + "' AND '"
					+ endTime + "' ");
		}
	

		sbSql.append(" group by m1.JIEDAO");
		System.err.println("#########: "+sbSql.toString());
		return sbSql.toString();
	}
	
}